﻿namespace AnyOffice.DAL
{
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class SysLogDao
    {
        public void Delete(SqlConnection conn, int logId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM\t[dbo].[Sys_Log] ");
            builder.Append("WHERE ");
            builder.Append("\t[LogId] = @LogId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@LogId", SqlDbType.Int) };
            commandParameters[0].Value = logId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
        }

        public void DeleteAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("TRUNCATE TABLE [dbo].[Sys_Log] ");
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString());
        }

        public DataTable SelectTop20(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\tTOP 20 ");
            builder.Append("\t[Sys_Log].[UserId], ");
            builder.Append("\t[Sys_Log].[LogTime], ");
            builder.Append("\t[Sys_Log].[Ip], ");
            builder.Append("\t[Sys_Log].[Remark], ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Sys_Code].[CodeName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Sys_Log] ");
            builder.Append("\tLEFT OUTER JOIN [dbo].[Users] ON [Sys_Log].[UserId] = [Users].[UserId] ");
            builder.Append("\tLEFT OUTER JOIN [dbo].[Sys_Code] ON CAST([Sys_Log].[Type] AS nvarchar(20)) = [Sys_Code].[CodeNo] ");
            builder.Append("WHERE ");
            builder.Append("\t[Sys_Code].[CodeFlag] = 'SYS_LOG' ");
            builder.Append("\t ");
            builder.Append("ORDER BY ");
            builder.Append("\t[LogId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Sys_Log";
            return set.Tables[0];
        }

        public DataTable Statistic(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DECLARE @TotalDays \t\tint ");
            builder.Append("DECLARE @TotalTimes\t\tint ");
            builder.Append("DECLARE @YearTimes\t\tint ");
            builder.Append("DECLARE @MonthTimes\tint ");
            builder.Append("DECLARE @DayTimes\t\tint ");
            builder.Append("DECLARE @DayAvgTimes\tint ");
            builder.Append("SELECT @TotalDays = DATEDIFF(Day,MIN([LogTime]),GETDATE())+1 FROM [dbo].[Sys_Log] WHERE [Type] = 1 ");
            builder.Append("SELECT @TotalTimes = COUNT(*) FROM [dbo].[Sys_Log]  WHERE [Type] = 1 ");
            builder.Append("SELECT @YearTimes = COUNT(*) FROM [dbo].[Sys_Log] WHERE Year([LogTime]) = Year(GETDATE())  AND [Type] = 1 ");
            builder.Append("SELECT @MonthTimes = COUNT(*) FROM [dbo].[Sys_Log] WHERE Year([LogTime]) = Year(GETDATE()) AND Month([LogTime]) = Month(GETDATE())  AND [Type] = 1 ");
            builder.Append("SELECT @DayTimes = COUNT(*) FROM [dbo].[Sys_Log] WHERE Year([LogTime]) = Year(GETDATE()) AND Month([LogTime]) = Month(GETDATE()) AND Day([LogTime]) = Day(GETDATE())  AND [Type] = 1 ");
            builder.Append("IF @TotalDays <> 0 ");
            builder.Append("\tSELECT @DayAvgTimes = @TotalTimes/@TotalDays ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT @DayAvgTimes = 0 ");
            builder.Append("SELECT ");
            builder.Append("\t@TotalDays AS TotalDays, ");
            builder.Append("\t@TotalTimes AS TotalTimes, ");
            builder.Append("\t@YearTimes AS YearTimes, ");
            builder.Append("\t@MonthTimes AS MonthTimes, ");
            builder.Append("\t@DayTimes AS DayTimes, ");
            builder.Append("\t@DayAvgTimes AS DayAvgTimes ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Sys_Log";
            return set.Tables[0];
        }
    }
}

